import Database from 'better-sqlite3';
import { join } from 'path';
import { mkdirSync, existsSync } from 'fs';
import { Todo } from '@/types/todo';

// 确保数据目录存在
const dataDir = join(process.cwd(), 'data');
if (!existsSync(dataDir)) {
  mkdirSync(dataDir, { recursive: true });
}

// 连接数据库
const dbPath = join(dataDir, 'todos.db');
const db = new Database(dbPath);

// 初始化数据库表
db.prepare(`
  CREATE TABLE IF NOT EXISTS todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    text TEXT NOT NULL,
    completed BOOLEAN DEFAULT 0,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    userId INTEGER,
    FOREIGN KEY (userId) REFERENCES users(id)
  )
`).run();

// 检查并添加 userId 列（如果不存在）
try {
  const columns = db.prepare("PRAGMA table_info(todos)").all();
  const hasUserIdColumn = columns.some((column: any) => column.name === 'userId');
  
  if (!hasUserIdColumn) {
    db.prepare("ALTER TABLE todos ADD COLUMN userId INTEGER REFERENCES users(id)").run();
  }
} catch (error) {
  console.error('Error checking or adding userId column:', error);
}

// 创建用户表
db.prepare(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`).run();

// 用户类型定义
export interface User {
  id: number;
  username: string;
  email: string;
  password: string;
  createdAt: string;
}

// 获取所有待办事项
export function getTodos(userId: number): Todo[] {
  try {
    const stmt = db.prepare('SELECT id, text, completed, createdAt FROM todos WHERE userId = ? ORDER BY createdAt DESC');
    return stmt.all(userId) as Todo[];
  } catch (error) {
    console.error('Error getting todos:', error);
    throw error;
  }
}

// 添加新的待办事项
export function addTodo(text: string, userId: number): Todo {
  if (!text || typeof text !== 'string' || !text.trim()) {
    throw new Error('Todo text must be a non-empty string');
  }
  
  try {
    // 插入新记录，确保参数类型正确
    const insertStmt = db.prepare(
      'INSERT INTO todos (text, completed, userId) VALUES (?, ?, ?)'
    );
    const result = insertStmt.run(text.trim(), 0, userId); // 使用 0/1 而不是布尔值
    
    // 获取刚插入的记录
    const selectStmt = db.prepare('SELECT id, text, completed, createdAt FROM todos WHERE id = ?');
    const newTodo = selectStmt.get(Number(result.lastInsertRowid)) as Todo;
    
    return newTodo;
  } catch (error) {
    console.error('Error adding todo:', error);
    throw error;
  }
}

// 更新待办事项状态
export function toggleTodo(id: number, completed: boolean): Todo | null {
  try {
    // 确保参数类型正确
    const numericId = Number(id);
    const numericCompleted = completed ? 1 : 0; // 使用 0/1 而不是布尔值
    
    // 更新记录
    const updateStmt = db.prepare(
      'UPDATE todos SET completed = ? WHERE id = ?'
    );
    const result = updateStmt.run(numericCompleted, numericId);
    
    // 检查是否有记录被更新
    if (result.changes === 0) {
      return null;
    }
    
    // 获取更新后的记录
    const selectStmt = db.prepare('SELECT id, text, completed, createdAt FROM todos WHERE id = ?');
    const updatedTodo = selectStmt.get(numericId) as Todo;
    
    return updatedTodo;
  } catch (error) {
    console.error(`Error toggling todo ${id}:`, error);
    throw error;
  }
}

// 删除待办事项
export function deleteTodo(id: number): boolean {
  try {
    // 确保参数类型正确
    const numericId = Number(id);
    
    const stmt = db.prepare('DELETE FROM todos WHERE id = ?');
    const result = stmt.run(numericId);
    return result.changes > 0;
  } catch (error) {
    console.error(`Error deleting todo ${id}:`, error);
    throw error;
  }
}

// 用户注册
export function registerUser(username: string, email: string, password: string): User {
  try {
    const stmt = db.prepare(
      'INSERT INTO users (username, email, password) VALUES (?, ?, ?)'
    );
    const result = stmt.run(username, email, password);
    
    const selectStmt = db.prepare('SELECT id, username, email, password, createdAt FROM users WHERE id = ?');
    const user = selectStmt.get(Number(result.lastInsertRowid)) as User;
    
    return user;
  } catch (error: any) {
    if (error.message.includes('UNIQUE constraint failed')) {
      throw new Error('Username or email already exists');
    }
    console.error('Error registering user:', error);
    throw error;
  }
}

// 用户登录验证
export function validateUser(email: string, password: string): User | null {
  try {
    const stmt = db.prepare('SELECT id, username, email, password, createdAt FROM users WHERE email = ? AND password = ?');
    const user = stmt.get(email, password) as User | undefined;
    return user || null;
  } catch (error) {
    console.error('Error validating user:', error);
    throw error;
  }
}

// 根据ID获取用户
export function getUserById(id: number): User | null {
  try {
    const stmt = db.prepare('SELECT id, username, email, createdAt FROM users WHERE id = ?');
    const user = stmt.get(id) as User | undefined;
    return user || null;
  } catch (error) {
    console.error('Error getting user by ID:', error);
    throw error;
  }
}

// 根据ID获取待办事项（验证用户权限）
export function getTodoByIdAndUser(id: number, userId: number): Todo | null {
  try {
    const stmt = db.prepare('SELECT id, text, completed, createdAt FROM todos WHERE id = ? AND userId = ?');
    const todo = stmt.get(id, userId) as Todo | undefined;
    return todo || null;
  } catch (error) {
    console.error(`Error getting todo ${id} for user ${userId}:`, error);
    throw error;
  }
}

// 更新待办事项（验证用户权限）
export function updateTodoByIdAndUser(id: number, updates: Partial<Todo>, userId: number): Todo | null {
  try {
    // 构建动态更新语句
    const setClause = [];
    const values = [];
    
    if (updates.text !== undefined) {
      setClause.push('text = ?');
      values.push(updates.text);
    }
    
    if (updates.completed !== undefined) {
      setClause.push('completed = ?');
      values.push(updates.completed ? 1 : 0);
    }
    
    if (setClause.length === 0) {
      throw new Error('No updates provided');
    }
    
    values.push(id, userId);
    
    const stmt = db.prepare(`
      UPDATE todos 
      SET ${setClause.join(', ')} 
      WHERE id = ? AND userId = ?
    `);
    
    const result = stmt.run(...values);
    
    if (result.changes === 0) {
      return null;
    }
    
    // 获取更新后的记录
    const selectStmt = db.prepare('SELECT id, text, completed, createdAt FROM todos WHERE id = ?');
    const updatedTodo = selectStmt.get(id) as Todo;
    
    return updatedTodo;
  } catch (error) {
    console.error(`Error updating todo ${id} for user ${userId}:`, error);
    throw error;
  }
}

// 删除待办事项（验证用户权限）
export function deleteTodoByIdAndUser(id: number, userId: number): boolean {
  try {
    const stmt = db.prepare('DELETE FROM todos WHERE id = ? AND userId = ?');
    const result = stmt.run(id, userId);
    return result.changes > 0;
  } catch (error) {
    console.error(`Error deleting todo ${id} for user ${userId}:`, error);
    throw error;
  }
}

export default db;